package com.xinhe.web.reposity;

import com.xinhe.web.entity.WithdrawalsRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.List;
import java.util.Set;

public interface WithdrawalsRecordRepository extends JpaRepository<WithdrawalsRecord, Long> {
    @Query(value = "select ifnull(sum(money),0) from tb_withdrawals_record where removed = 0 and f_user_id=?1", nativeQuery = true)
    BigDecimal queryWithdrawalsMoney(Long userId);
    //昨天
    @Query(value = "select count(1) from tb_withdrawals_record where removed=0 and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1 and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    Integer findBySumCountYest();
    @Query(value = "select ifnull(sum(money),0) from tb_withdrawals_record where removed=0 and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1 and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    BigDecimal findBySumMoneyYest();
    @Query(value = "select ifnull(sum(service_fee),0) from tb_withdrawals_record where removed=0 and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1 and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    BigDecimal findBySumServiceFeeYest();
    @Query(value = "select ifnull(sum(wx_pay_money),0) from tb_withdrawals_record where removed=0 and TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1 and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    BigDecimal findBySumRealityAccountFeeYest();

    //今天
    @Query(value = "select count(1) from tb_withdrawals_record where removed=0 and to_days(create_time) = to_days(now()) and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    Integer findBySumCountToday();
    @Query(value = "select ifnull(sum(money),0) from tb_withdrawals_record where removed=0 and to_days(create_time) = to_days(now()) and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    BigDecimal findBySumMoneyToday();
    @Query(value = "select ifnull(sum(service_fee),0) from tb_withdrawals_record where removed=0 and to_days(create_time) = to_days(now()) and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    BigDecimal findBySumServiceFeeToday();
    @Query(value = "select ifnull(sum(wx_pay_money),0) from tb_withdrawals_record where removed=0 and to_days(create_time) = to_days(now()) and status in('IN_TRANSFER_ACCOUNTS','TRANSFER_ACCOUNTS_SUCCESS')", nativeQuery = true)
    BigDecimal findBySumRealityAccountFeeToday();
    @Query(value = "select ifnull(sum(money),0) from tb_withdrawals_record where removed = 0 and status='TRANSFER_ACCOUNTS_SUCCESS' ", nativeQuery = true)
    BigDecimal findByThisMonthMoney();

    @Query(value = "select count(1) from tb_withdrawals_record where removed=0 and f_user_id=?1 and to_days(create_time)=to_days(now()) and  status in ('IN_TRANSFER_ACCOUNTS','APPLY')", nativeQuery = true)
    Integer findByUserIdAndDays(Long fUserId);
    @Query(value = "select * from tb_withdrawals_record where removed=0 and id in ?1", nativeQuery = true)
    List<WithdrawalsRecord> findByIdIn(Set<Long> transferAccountsIds);
    @Query(value = "select * from tb_withdrawals_record where removed = 0 and status='IN_TRANSFER_ACCOUNTS' and  mode ='PAY_TO_BANK_CARD'", nativeQuery = true)
    List<WithdrawalsRecord> findByAllAndType();


    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1 and to_days(create_time)=to_days(now()) ),0) money ", nativeQuery = true)
    BigDecimal findMoney1(Long userId);

    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1 and TO_DAYS( NOW() ) - TO_DAYS( create_time) = 1 ),0) money ", nativeQuery = true)
    BigDecimal findMoney2(Long userId);

    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1 and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' ) ),0) money ", nativeQuery = true)
    BigDecimal findMoney3(Long userId);

    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1),0) money ", nativeQuery = true)
    BigDecimal findMoney4(Long userId);





    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1  and to_days(create_time)=to_days(now())),0) money", nativeQuery = true)
    BigDecimal findMoney7(Long userId);

    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1  and TO_DAYS( NOW() ) - TO_DAYS( create_time) = 1),0) money", nativeQuery = true)
    BigDecimal findMoney8(Long userId);

    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1  and DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )),0) money", nativeQuery = true)
    BigDecimal findMoney9(Long userId);

    @Query(value = "select IFNULL((select sum(money) from tb_withdrawals_record where status='TRANSFER_ACCOUNTS_SUCCESS' and f_user_id=?1),0) money", nativeQuery = true)
    BigDecimal findMoney10(Long userId);

    @Query(value = "select ifnull(sum(reality_account_fee),0) from tb_withdrawals_record where removed=0  and status in ('TRANSFER_ACCOUNTS_SUCCESS','IN_TRANSFER_ACCOUNTS','APPLY') and f_user_id=?1 and to_days(create_time)=to_days(now())", nativeQuery = true)
    BigDecimal findByUserIdAndSumToDayMoney(Long userId);
    @Query(value = "select ifnull(sum(money),0) from tb_withdrawals_record where removed = 0 and status=?2 and f_user_id =?1 ", nativeQuery = true)
    BigDecimal findByUserIdAndStatus(Long userId, String transferAccountsSuccess);

    @Query(value = "select ifnull(sum(reality_account_fee),0) from tb_withdrawals_record where removed=0  and status = 'TRANSFER_ACCOUNTS_SUCCESS'  and f_user_id=?1  ", nativeQuery = true)
    BigDecimal findBySellerShopSumMoney(Long aLong);
}
